﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ReceiveControl_BLL : IProjectControl
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProject(int Year, string HeaderID, string StrategySeq, string SourceID)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@seq", HeaderID);
                dbManager.AddParameters(1, "@strat_seq", StrategySeq);
                dbManager.AddParameters(2, "@year", Year);
                dbManager.AddParameters(3, "@source", SourceID);
                dbManager.AddParameters(4, "@RecordStatus", SystemInfo._DeleteRecordStatus);

                string sql = "select a.bgm_rec_seq as bgm_project_seq, a.bgm_rec_name as bgm_project_name"
                                + ", null as bgm_project_ctrl_seq"
                                + ", j.job_site_seq, j.job_site_name"
                                + ", sum(coalesce(e.total_amount, 0)) as total_amount"
                                + ", sum(coalesce(e.approve_amount, 0)) as approve_amount"
                                + " from bgm_receive_header h"
                                   + " left join bgm_receive a on h.bgm_rec_header_seq = a.bgm_rec_header_seq"
                                   + " left join bgm_receive_expense e on e.bgm_rec_seq = a.bgm_rec_seq"
                                   + " left join hrm_mp_job_sites j on a.job_site_seq = j.job_site_seq"
                                + " where h.bgm_rec_header_seq = @seq"
                                    + " and h.bgm_year = @year"
                                    + " and e.bgm_source_seq = @source"
                                    + " and a.bgm_strat_list_seq = @strat_seq"
                                    + " and a.record_status not like @RecordStatus"
                                + " group by a.bgm_rec_seq, a.bgm_rec_name, j.job_site_seq, j.job_site_name";
                
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataStrategyControl(int Year, string HeaderID, bool groupbyproject, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@seq", HeaderID);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@RecordStatus", RecordStatus);

                string sql = "select d.bgm_strat_list_seq, d.bgm_strat_list_name as name"
                                + ", coalesce(sum(a.total_amount), 0) as total_amount"
                                + ", coalesce(sum(a.approve_amount), 0) as approve_amount"
                                + " from bgm_receive_header h"
                                   + " inner join bgm_receive_control a on h.bgm_rec_header_seq = a.bgm_rec_header_seq"
                                   + " inner join bgm_strategy_data_list d on d.bgm_strat_list_seq = a.bgm_strat_list_seq"
                                + " where h.bgm_rec_header_seq = @seq"
                                    + " and h.bgm_year = @year"
                                    + " and a.record_status = @RecordStatus"
                                + " group by d.bgm_strat_list_seq, d.bgm_strat_list_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectByOption(int Year, string HeaderID, string Strategy, string RecordStatus)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@seq", HeaderID);
                dbManager.AddParameters(1, "@year", Year);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@RecordStatus", RecordStatus);

                string sql = "select mo.bgm_option_seq, mo.bgm_option_name as name"
                            + ", sum((weight * coalesce(a.total_amount, 0)) / 100) as total_amount"
                            + ", sum((weight * coalesce(a.approve_amount, 0)) / 100) as approve_amount"
                            + " from bgm_receive a"
                               + " inner join bgm_receive_option o on o.bgm_rec_seq = a.bgm_rec_seq"
                               + " inner join bgm_mas_option mo on mo.bgm_option_seq = o.bgm_option_seq"
                            + " where a.bgm_rec_header_seq = @seq"
                                + " and mo.bgm_option_type_seq = 3"
                                + " and a.bgm_year = @year"
                                + ((Strategy.Length > 0) ? " and a.bgm_strat_list_seq = @strategy" : "")
                                + " and a.record_status = @RecordStatus"
                            + " group by mo.bgm_option_seq, mo.bgm_option_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string seq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@strategy", Strategy);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                string sql = "select s.bgm_source_seq, s.bgm_source_name"
                            + ", coalesce(sum(ae.total_amount), 0) as total_amount"
                            + ", coalesce(sum(ae.approve_amount), 0) as approve_amount"
                            + " from  bgm_mas_source s "
                               + " left join ( 	select ae.bgm_source_seq, ae.total_amount, ae.approve_amount"
                                                + " from bgm_receive_control_expense ae"
                                                    + " inner join bgm_receive_control ah on ah.bgm_rec_ctrl_seq = ae.bgm_rec_ctrl_seq"
                                                + " where ah.bgm_rec_header_seq = @seq"
                                                    + ((Strategy.Length > 0) ? " and ah.bgm_strat_list_seq = @strategy" : "")
                                            + " ) ae on ae.bgm_source_seq = s.bgm_source_seq"
                            + " where s.record_status = @RecordStatus"
                               + " and s.bgm_source_seq not in (select upper_bgm_source_seq from bgm_mas_source where record_status = @RecordStatus and upper_bgm_source_seq is not null)"
                            + " group by s.bgm_source_seq, s.bgm_source_name"
                            + " order by s.bgm_source_code";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequenceForGridIn(string Year, string seq, string Strategy, string RecordStatus)
        {
            try
            {

                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpGrpBySequenceForGridIn(string Year, string seq, string Strategy, string RecordStatus)
        {
            return null;
        }

        public DataTable GetDataProjectOption(string bgm_app_header_seq, string bgm_option_type_seq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@seq", bgm_app_header_seq);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@status", SystemInfo._DeleteRecordStatus);

                string sql = "select sum(approve_amount) as total"
                            + " from ( "
                                + " select a.bgm_rec_header_seq, ao.bgm_option_seq, mo.bgm_option_name"
                                + ", null as weight"
                                + ", sum((weight * coalesce(a.total_amount, 0)) / 100) as total_amount"
                                + ", sum((weight * coalesce(a.approve_amount, 0)) / 100) as approve_amount"
                                + " from bgm_receive a"
                                    + " inner join bgm_receive_option ao on a.bgm_rec_seq = ao.bgm_rec_seq"
                                    + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                                + " where a.bgm_rec_header_seq = @seq"
                                    + ((Strategy.Length > 0) ? " and a.bgm_strat_list_seq = @strategy" : "")
                                    + " and mo.bgm_option_type_seq = @type"
                                    + " and a.record_status not like @status"
                                + " group by  a.bgm_rec_header_seq, ao.bgm_option_seq , mo.bgm_option_name"
                            + " ) t"
                            + " group by bgm_rec_header_seq";

                DataSet ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                string total = (ds.Tables[0].Rows.Count > 0) ? ds.Tables[0].Rows[0][0].ToString() : "1";

                sql = "select a.bgm_rec_header_seq, ao.bgm_option_seq, mo.bgm_option_name"
                            + ", (sum((weight * coalesce(a.approve_amount, 0)) / 100) * 100) / " + total + " as weight"
                            + ", sum((weight * coalesce(a.total_amount, 0)) / 100) as total_amount"
                            + ", sum((weight * coalesce(a.approve_amount, 0)) / 100) as approve_amount"
                            + " from bgm_receive a"
                                + " inner join bgm_receive_option ao on a.bgm_rec_seq = ao.bgm_rec_seq"
                                + " inner join bgm_mas_option mo on mo.bgm_option_seq = ao.bgm_option_seq"
                            + " where a.bgm_rec_header_seq = @seq"
                                + ((Strategy.Length > 0) ? " and a.bgm_strat_list_seq = @strategy" : "")
                                + " and mo.bgm_option_type_seq = @type"
                                + " and a.record_status not like @status"
                            + " group by  a.bgm_rec_header_seq, ao.bgm_option_seq , mo.bgm_option_name";

                DataTable dt = new DataTable();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectOption(string Year, string JobSiteID, string bgm_option_type_seq, string Strategy)
        {
            try
            {
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@jobsite", JobSiteID);
                dbManager.AddParameters(1, "@type", bgm_option_type_seq);
                dbManager.AddParameters(2, "@strategy", Strategy);
                dbManager.AddParameters(3, "@year", Year);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);

                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectOptionBySequence(string seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPI(string bgm_app_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectKPIBySequence(string bgm_app_kpi_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlan(string bgm_app_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanBySequence(string bgm_app_act_plan_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectActivityPlanTarget(string bgm_app_act_plan_seq)
        {
            try
            {
                string sql = "select bgm_app_act_plan_target_seq as Sequence, target, start_month, end_month"
                       + " from bgm_approval_activity_plan_target"
                       + " where bgm_app_act_plan_seq =" + bgm_app_act_plan_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListPRojectExpense(string bgm_app_ctrl_seq, string source)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", bgm_app_ctrl_seq);
                dbManager.AddParameters(2, "@source", source);

                string sql = " select e1.bgm_exp_seq as value, e1.bgm_exp_name as text"
                            + ", e2.bgm_exp_seq as bgm_exp_seq_2, e2.bgm_exp_name as bgm_exp_name_2"
                            + ", e3.bgm_exp_seq as bgm_exp_seq_3, e3.bgm_exp_name as bgm_exp_name_3"
                            + ", eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name"
                            + ", sum(re.total_amount) as total_amount, re.bgm_app_ctrl_exp_seq"
                        + " from bgm_mas_expense e"
                            + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense e2 on e2.bgm_exp_seq = e1.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense e3 on e3.bgm_exp_seq = e2.upper_bgm_exp_seq"
                            + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                            + " left join bgm_approval_control_expense re on re.bgm_exp_seq = e.bgm_exp_seq and re.bgm_app_ctrl_seq = @seq and re.bgm_source_seq = @source"
                        + " where e.bgm_exp_seq not in ( select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                            + " and e.record_status = @RecordStatus"
                        + " group by e.bgm_exp_seq, e.bgm_exp_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_code, eg.bgm_exp_grp_name"
                            + ", e1.bgm_exp_seq, e1.bgm_exp_name, e2.bgm_exp_seq, e2.bgm_exp_name, e3.bgm_exp_seq, e3.bgm_exp_name"
                        + " order by eg.bgm_exp_grp_code, e.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectExpense(string bgm_strat_list_seq, string source)
        {
            try
            {
                string sql = "select eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name"
                           + ", e.bgm_exp_seq as value, e.bgm_exp_name as text"
                            + ", e1.bgm_exp_seq as bgm_exp_seq_1, e1.bgm_exp_name as bgm_exp_name_1"
                            + ", e2.bgm_exp_seq as bgm_exp_seq_2, e2.bgm_exp_name as bgm_exp_name_2"
                            + ", e3.bgm_exp_seq as bgm_exp_seq_3, e3.bgm_exp_name as bgm_exp_name_3"
                           + ", re.bgm_rec_exp_seq as Sequence, re.bgm_source_seq, s.bgm_source_name"
                           + ", re.bgm_exp_seq, re.total_amount, re.approve_amount"
                           + " from bgm_receive r"
                               + " inner join bgm_receive_expense re on re.bgm_rec_seq = r.bgm_rec_seq"
                               + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                               + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                               + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                               + " left join bgm_mas_expense e2 on e2.bgm_exp_seq = e1.upper_bgm_exp_seq"
                               + " left join bgm_mas_expense e3 on e3.bgm_exp_seq = e2.upper_bgm_exp_seq"
                               + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " where r.bgm_strat_list_seq = " + bgm_strat_list_seq
                           + " and re.bgm_source_seq = " + source;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseBySequence(string bgm_app_ctrl_exp_seq)
        {
            try
            {
                //dbManager.CreateParameters(1);
                //dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql = "select s.bgm_source_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_name, e1.bgm_exp_name, e.bgm_exp_name, i.icm_res_item_name, re.*"
                           + " from bgm_approval_control_expense re"
                           + " left join bgm_mas_source s on re.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " left join bgm_mas_expense e1 on e1.bgm_exp_seq = e.upper_bgm_exp_seq"
                           + " left join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join icm_mas_resource_item i on i.icm_res_item_seq = re.icm_res_item_seq"

                           + " where re.bgm_app_ctrl_exp_seq =" + bgm_app_ctrl_exp_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpenseGroup(string bgm_app_ctrl_seq, string bgm_source_seq)
        {
            try
            {
                string sql = "select eg.bgm_exp_grp_name, coalesce(sum(re.total_amount), 0) as total_amount"
                           + " from bgm_mas_expense_group eg"
                           + " left join bgm_mas_source s on eg.bgm_source_seq = s.bgm_source_seq"
                           + " left join bgm_mas_expense e on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                           + " left join bgm_approval_control_expense re on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " where eg.record_status = 'U'"
                           + " and re.bgm_app_ctrl_seq = " + bgm_app_ctrl_seq
                           + " and re.bgm_source_seq = " + bgm_source_seq
                           + " group by eg.bgm_exp_grp_name, eg.bgm_exp_grp_seq";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlan(string bgm_app_ctrl_seq, string bgm_source_seq)
        {
            try
            {
                string sql = "select rp.bgm_app_ctrl_exp_plan_seq as Sequence, e.bgm_exp_name, re.bgm_app_ctrl_exp_seq as bgm_prj_exp_seq, re.total_amount"
                           + " , rp.amount_month1, rp.amount_month2, rp.amount_month3, rp.amount_month4"
                           + " , rp.amount_month5, rp.amount_month6, rp.amount_month7, rp.amount_month8"
                           + " , rp.amount_month9, rp.amount_month10, rp.amount_month11, rp.amount_month12"
                           + " from bgm_approval_control r"
                           + " left join bgm_approval_control_expense re on r.bgm_app_ctrl_seq = re.bgm_app_ctrl_seq"
                           + " left join bgm_approval_control_expense_plan rp on rp.bgm_app_ctrl_exp_seq = re.bgm_app_ctrl_exp_seq"
                           + " left join bgm_mas_expense e on re.bgm_exp_seq = e.bgm_exp_seq"
                           + " where r.bgm_app_ctrl_seq = " + bgm_app_ctrl_seq
                           + " and re.bgm_source_seq = " + bgm_source_seq;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectExpensePlanBySequence(string bgm_app_ctrl_exp_plan_seq)
        {
            try
            {
                string sql = "select ep.*,re.total_amount"
                           + " from bgm_approval_control_expense_plan ep"
                           + " inner join bgm_approval_control_expense re on re.bgm_app_ctrl_exp_seq = ep.bgm_app_ctrl_exp_seq"
                           + " where bgm_app_ctrl_exp_plan_seq = " + bgm_app_ctrl_exp_plan_seq;


                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertProject(string bgm_app_header_seq, string bgm_year, DateTime bgm_app_date, string bgm_strat_list_seq, string job_site_seq, string prj_type_seq, string bgm_app_manager, string bgm_app_scope, string bgm_app_code, string bgm_app_name, string bgm_app_descr, string bgm_app_reason, string bgm_app_object, string bgm_app_plan, string bgm_app_target, string bgm_app_remark, string CreatedBy)
        {
            try
            {
                return 1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectCustoms(string bgm_app_seq, string prj_ctrl_seq, string descr)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectOption(string bgm_app_seq, string descr, Nullable<Double> weight, string bgm_option_seq, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectKPI(string bgm_app_kpi_name, string bgm_app_kpi_descr, string unit, string bgm_app_kpi_weight, string bgm_app_seq, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type, string target_q1, string target_q2, string target_q3, string target_q4, string mtef_1, string mtef_2, string mtef_3, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int InsertProjectActivityPlan(string bgm_app_seq, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string created_by)
        {
            try
            {
                return 1;
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectActivityPlanTarget(string bgm_app_act_plan_seq, string target, string start_month, string end_month, string created_by)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectExpense(string bgm_app_ctrl_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@bgm_app_ctrl_seq", bgm_app_ctrl_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@descr", descr);

                dbManager.AddParameters(5, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(6, "@unit_qty", unit_qty);
                dbManager.AddParameters(7, "@unit_price", unit_price);
                dbManager.AddParameters(8, "@mtef1", mtef1);
                dbManager.AddParameters(9, "@mtef2", mtef2);
                dbManager.AddParameters(10, "@mtef3", mtef3);
                dbManager.AddParameters(11, "@created_by", created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_approval_control_expense ("
                                            + "bgm_app_ctrl_seq,"
                                            + "bgm_source_seq,"
                                            + "bgm_exp_seq,"
                                            + "total_amount,"
                                            + "descr,"
                                            + "icm_res_item_seq,"
                                            + "unit_qty,"
                                            + "unit_price,"
                                            + "mtef1,"
                                            + "mtef2,"
                                            + "mtef3,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_app_ctrl_seq,"
                                            + "@bgm_source_seq,"
                                            + "@bgm_exp_seq,"
                                            + "@total_amount,"
                                            + "@descr,"
                                            + "@icm_res_item_seq,"
                                            + "@unit_qty,"
                                            + "@unit_price,"
                                            + "@mtef1,"
                                            + "@mtef2,"
                                            + "@mtef3,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectExpensePlan(string bgm_app_ctrl_exp_seq, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@bgm_app_ctrl_exp_seq", bgm_app_ctrl_exp_seq);
                dbManager.AddParameters(1, "@amount_month1", amount_month1 != "" ? amount_month1 : "0");
                dbManager.AddParameters(2, "@amount_month2", amount_month2 != "" ? amount_month2 : "0");
                dbManager.AddParameters(3, "@amount_month3", amount_month3 != "" ? amount_month3 : "0");
                dbManager.AddParameters(4, "@amount_month4", amount_month4 != "" ? amount_month4 : "0");
                dbManager.AddParameters(5, "@amount_month5", amount_month5 != "" ? amount_month5 : "0");
                dbManager.AddParameters(6, "@amount_month6", amount_month6 != "" ? amount_month6 : "0");
                dbManager.AddParameters(7, "@amount_month7", amount_month7 != "" ? amount_month7 : "0");
                dbManager.AddParameters(8, "@amount_month8", amount_month8 != "" ? amount_month8 : "0");
                dbManager.AddParameters(9, "@amount_month9", amount_month9 != "" ? amount_month9 : "0");
                dbManager.AddParameters(10, "@amount_month10", amount_month10 != "" ? amount_month10 : "0");
                dbManager.AddParameters(11, "@amount_month11", amount_month11 != "" ? amount_month11 : "0");
                dbManager.AddParameters(12, "@amount_month12", amount_month12 != "" ? amount_month12 : "0");
                dbManager.AddParameters(13, "@created_by", created_by);
                dbManager.AddParameters(14, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_approval_control_expense_plan ("
                                            + "bgm_app_ctrl_exp_seq,"
                                            + "amount_month1,"
                                            + "amount_month2,"
                                            + "amount_month3,"
                                            + "amount_month4,"
                                            + "amount_month5,"
                                            + "amount_month6,"
                                            + "amount_month7,"
                                            + "amount_month8,"
                                            + "amount_month9,"
                                            + "amount_month10,"
                                            + "amount_month11,"
                                            + "amount_month12,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_app_ctrl_exp_seq,"
                                            + "@amount_month1,"
                                            + "@amount_month2,"
                                            + "@amount_month3,"
                                            + "@amount_month4,"
                                            + "@amount_month5,"
                                            + "@amount_month6,"
                                            + "@amount_month7,"
                                            + "@amount_month8,"
                                            + "@amount_month9,"
                                            + "@amount_month10,"
                                            + "@amount_month11,"
                                            + "@amount_month12,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {
                //return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProject(string project_sequence, string control_seq, string status, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@projseq", project_sequence);
                dbManager.AddParameters(1, "@ctrlseq", control_seq);
                dbManager.AddParameters(2, "@status", status);
                dbManager.AddParameters(3, "@cancel_status", SystemInfo._CancelRecordStatus);
                dbManager.AddParameters(4, "@updated_by", UpdateBy);
                dbManager.AddParameters(5, "@updated_when", DateTime.Now);

                string strsql = "UPDATE bgm_approval_control"
                    + " SET  record_status = @status"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE bgm_app_ctrl_seq = @ctrlseq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectOption(string Sequence, string descr, Nullable<Double> weight, string bgm_option_seq, string UpdateBy)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectKPI(string Sequence, string bgm_app_kpi_name, string bgm_app_kpi_descr, string unit, string bgm_app_kpi_weight, string bgm_kpi_type_seq, string score_1, string score_2, string score_3, string score_4, string score_5, string target_type, string target_q1, string target_q2, string target_q3, string target_q4, string mtef_1, string mtef_2, string mtef_3, string UpdateBy)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectActivityPlan(string Sequence, string descr, int act_month1, int act_month2, int act_month3, int act_month4, int act_month5, int act_month6, int act_month7, int act_month8, int act_month9, int act_month10, int act_month11, int act_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(16);
                dbManager.AddParameters(0, "@descr", descr); //bgm_app_date
                dbManager.AddParameters(1, "@act_month1", act_month1);
                dbManager.AddParameters(2, "@act_month2", act_month2);

                dbManager.AddParameters(3, "@act_month3", act_month3);
                dbManager.AddParameters(4, "@act_month4", act_month4);
                dbManager.AddParameters(5, "@act_month5", act_month5);
                dbManager.AddParameters(6, "@act_month6", act_month6);
                dbManager.AddParameters(7, "@act_month7", act_month7);
                dbManager.AddParameters(8, "@act_month8", act_month8);
                dbManager.AddParameters(9, "@act_month9", act_month9);
                dbManager.AddParameters(10, "@act_month10", act_month10);
                dbManager.AddParameters(11, "@act_month11", act_month11);
                dbManager.AddParameters(12, "@act_month12", act_month12);
                dbManager.AddParameters(13, "@updated_by", UpdateBy);
                dbManager.AddParameters(14, "@updated_when", DateTime.Now);
                dbManager.AddParameters(15, "@sequence", Sequence);


                string strsql = "UPDATE bgm_approval_activity_plan SET "
                                + " descr=@descr,"
                                + " act_month1=@act_month1,"
                                + " act_month2=@act_month2,"
                                + " act_month3=@act_month3,"
                                + " act_month4=@act_month4,"
                                + " act_month5=@act_month5,"
                                + " act_month6=@act_month6,"
                                + " act_month7=@act_month7,"
                                + " act_month8=@act_month8,"
                                + " act_month9=@act_month9,"
                                + " act_month10=@act_month10,"
                                + " act_month11=@act_month11,"
                                + " act_month12=@act_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_app_act_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectActivityPlanTarget(string Sequence, string target, string start_month, string end_month, string UpdateBy)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpense(string Sequence, string bgm_app_ctrl_exp_seq, string bgm_source_seq, string bgm_exp_seq, string approve_amount, string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price, Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(15);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@ctrlseq", bgm_app_ctrl_exp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(4, "@approve_amount", approve_amount);
                dbManager.AddParameters(5, "@descr", descr);
                dbManager.AddParameters(6, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(7, "@unit_qty", unit_qty);
                dbManager.AddParameters(8, "@unit_price", unit_price);
                dbManager.AddParameters(9, "@mtef1", mtef1);
                dbManager.AddParameters(10, "@mtef2", mtef2);
                dbManager.AddParameters(11, "@mtef3", mtef3);
                dbManager.AddParameters(12, "@updated_by", UpdateBy);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                dbManager.AddParameters(14, "@delete_status", SystemInfo._DeleteRecordStatus);


                // ------ เฉพาะ DEP -----------------------------------

                string strsql = "UPDATE bgm_approval_control_expense"
                        + " SET approve_amount = @approve_amount"
                            + ", updated_by = @updated_by"
                            + ", updated_when = @updated_when"
                        + " WHERE bgm_app_ctrl_seq = @ctrlseq"
                            + " and bgm_source_seq = @bgm_source_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                strsql = "UPDATE bgm_approval_control"
                    + " SET approve_amount = (select sum(approve_amount)"
                                    + " from bgm_approval_control_expense s"
                                    + " where s.bgm_app_ctrl_seq = bgm_approval_control.bgm_app_ctrl_seq)"
                        + ", updated_by = @updated_by"
                        + ", updated_when = @updated_when"
                    + " WHERE bgm_app_ctrl_seq = @ctrlseq";

                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                // ---------------------------------------------------

                // ------ ถ้าปรับระดับค่าใช้จ่ายใช้อันนี้ ------------------------

                //string strsql = "UPDATE bgm_approval_control_expense"
                //        + " SET bgm_source_seq = @bgm_source_seq"
                //            + ", bgm_exp_seq = @bgm_exp_seq"
                //            + ", approve_amount = @approve_amount"
                //            + ", descr = @descr"
                //            + ", icm_res_item_seq = @icm_res_item_seq"
                //            + ", unit_qty = @unit_qty"
                //            + ", unit_price = @unit_price"
                //            + ", mtef1 = @mtef1"
                //            + ", mtef2 = @mtef2"
                //            + ", mtef3 = @mtef3"
                //            + ", updated_by = @updated_by"
                //            + ", updated_when = @updated_when"
                //        + " WHERE bgm_app_ctrl_exp_seq=@sequence";
                //dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                // ------------------------------------------------
                dbManager.CommitTransaction();

            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectExpensePlan(string Sequence, string amount_month1, string amount_month2, string amount_month3, string amount_month4, string amount_month5, string amount_month6, string amount_month7, string amount_month8, string amount_month9, string amount_month10, string amount_month11, string amount_month12, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@amount_month1", amount_month1);
                dbManager.AddParameters(1, "@amount_month2", amount_month2);
                dbManager.AddParameters(2, "@amount_month3", amount_month3);
                dbManager.AddParameters(3, "@amount_month4", amount_month4);
                dbManager.AddParameters(4, "@amount_month5", amount_month5);
                dbManager.AddParameters(5, "@amount_month6", amount_month6);
                dbManager.AddParameters(6, "@amount_month7", amount_month7);
                dbManager.AddParameters(7, "@amount_month8", amount_month8);
                dbManager.AddParameters(8, "@amount_month9", amount_month9);
                dbManager.AddParameters(9, "@amount_month10", amount_month10);
                dbManager.AddParameters(10, "@amount_month11", amount_month11);
                dbManager.AddParameters(11, "@amount_month12", amount_month12);
                dbManager.AddParameters(12, "@updated_by", UpdateBy);
                dbManager.AddParameters(13, "@updated_when", DateTime.Now);
                dbManager.AddParameters(14, "@sequence", Sequence);


                string strsql = "UPDATE bgm_approval_control_expense_plan SET "
                                + " amount_month1=@amount_month1,"
                                + " amount_month2=@amount_month2,"
                                + " amount_month3=@amount_month3,"
                                + " amount_month4=@amount_month4,"
                                + " amount_month5=@amount_month5,"
                                + " amount_month6=@amount_month6,"
                                + " amount_month7=@amount_month7,"
                                + " amount_month8=@amount_month8,"
                                + " amount_month9=@amount_month9,"
                                + " amount_month10=@amount_month10,"
                                + " amount_month11=@amount_month11,"
                                + " amount_month12=@amount_month12,"
                                + " updated_by=@updated_by,"
                                + " updated_when=@updated_when WHERE bgm_app_ctrl_exp_plan_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateProjectCustoms(string Sequence, string descr)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProject(int Seq)
        {
            try
            {
               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectOption(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectKPI(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlan(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectActivityPlanTarget(string Seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProjectExpense(string Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_approval_control_expense WHERE bgm_app_ctrl_exp_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InsertProjectManager(string bgm_app_seq, string emp_seq)
        {
            try
            {
            }
            catch
            {
                // return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void DeleteProjectManager(string bgm_app_seq)
        {
            try
            {

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectManager(string bgm_prj_seq)
        {
            try
            {
                DataTable dt = new DataTable();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool chkDubProjectOption(string bgm_app_seq, string bgm_option_seq)
        {
            try
            {
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlByJobSite(string job_site_seq, string HeaderID)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._WorkRecordStatus);   
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                dbManager.AddParameters(2, "@HeaderID", HeaderID);

                string sql = "select r.bgm_app_ctrl_seq as Sequence"
                           + ", s.bgm_strat_list_name as Strategy "
                           + ", j.job_site_name"
                           + ", case when h.from_bgm_app_header_seq is not null then (select coalesce(sum(total_amount), 0) from bgm_approval_control s1 inner join bgm_approval_control_expense s2 on s1.bgm_app_ctrl_seq = s2.bgm_app_ctrl_seq where s1.bgm_app_header_seq = h.from_bgm_app_header_seq and s1.job_site_seq = r.job_site_seq and s1.bgm_strat_list_seq = r.bgm_strat_list_seq) else (select coalesce(sum(total_amount), 0) from bgm_request s1 inner join bgm_request_expense s2 on s1.bgm_req_seq = s2.bgm_req_seq where s1.bgm_req_seq in (select bgm_req_seq from bgm_approval where bgm_app_header_seq = h.bgm_app_header_seq ) and s1.job_site_seq = r.job_site_seq and s1.bgm_strat_list_seq = r.bgm_strat_list_seq) end as pre_total_amount"
                           + ", (select coalesce(sum(total_amount), 0) from bgm_approval_control_expense e where e.bgm_app_ctrl_seq = r.bgm_app_ctrl_seq) as total_amount"
                           + " from bgm_approval_control r"
                           + " left join bgm_approval_header h on r.bgm_app_header_seq = h.bgm_app_header_seq"
                           + " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq"
                           + " left join hrm_mp_job_sites j on r.job_site_seq = j.job_site_seq"
                           + " where r.bgm_app_header_seq = @HeaderID";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectControlBySequenceForGridIn(string seq, string job_site_seq)
        {
            try
            {
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(1, "@seq", seq);
                dbManager.AddParameters(2, "@job_site_seq", job_site_seq);

                string sql = "select s.bgm_source_name"
                           + ", case when h.from_bgm_app_header_seq is not null "
                           + " then (select coalesce(sum(total_amount), 0) from bgm_approval_control s1 inner join bgm_approval_control_expense s2 on s1.bgm_app_ctrl_seq = s2.bgm_app_ctrl_seq where s1.bgm_app_header_seq = h.from_bgm_app_header_seq and s2.bgm_source_seq = s.bgm_source_seq )"
                           + " else (select coalesce(sum(total_amount), 0) from bgm_request s1 inner join bgm_request_expense s2 on s1.bgm_req_seq = s2.bgm_req_seq "
                           + " where s1.bgm_req_seq in (select bgm_req_seq from bgm_approval where bgm_app_header_seq = h.bgm_app_header_seq ) and s2.bgm_source_seq = s.bgm_source_seq) end as pre_total_amount"
                           + " , coalesce(sum(ae.total_amount), 0) as total_amount"
                           + " from  bgm_approval_control r"
                           + " left join bgm_approval_header h on r.bgm_app_header_seq = h.bgm_app_header_seq"
                           + " left join bgm_approval_control_expense ae on ae.bgm_app_ctrl_seq = r.bgm_app_ctrl_seq"
                           + " left join bgm_mas_source s on ae.bgm_source_seq = s.bgm_source_seq"
                           + " where r.bgm_app_ctrl_seq = @seq"
                      //     + " and r.job_site_seq = @job_site_seq"
                           + " group by s.bgm_source_seq, s.bgm_source_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataControlChart(string Year, string SubAct, string Jobsite, string Project, string Source, string ExpenseGroup, string SeriesName)
        {
            try
            {
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);   // prj
                dbManager.AddParameters(1, "@Year", Year);
                dbManager.AddParameters(2, "@Jobsite", Jobsite);
                dbManager.AddParameters(3, "@SubAct", SubAct);
                dbManager.AddParameters(4, "@Project", Project);
                dbManager.AddParameters(5, "@Source", Source);
                dbManager.AddParameters(6, "@ExpenseGroup", ExpenseGroup);

                string strSqlSelect = "";
                string strSqlJoin = "";
                string strSqlGroupBy = "";

                switch (SeriesName)
                {
                    case "1":
                        strSqlSelect = " r.bgm_strat_list_seq"
                            + ", s.bgm_strat_list_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_strategy_data_list s on r.bgm_strat_list_seq = s.bgm_strat_list_seq";
                        strSqlGroupBy = " group by r.bgm_strat_list_seq"
                            + ", s.bgm_strat_list_name";
                        break;
                    case "2":
                        strSqlSelect = " r.job_site_seq"
                            + ", j.job_site_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join hrm_mp_job_sites j on r.job_site_seq = s.job_site_seq";
                        strSqlGroupBy = " group by r.job_site_seq"
                            + ", j.job_site_name";
                        break;
                    case "3":
                        strSqlSelect = " r.bgm_rec_seq"
                            + ", r.bgm_rec_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = "";
                        strSqlGroupBy = " group by r.bgm_rec_seq"
                            + ", r.bgm_rec_name";
                        break;
                    case "4":
                        strSqlSelect = " e.bgm_source_seq"
                            + ", s.bgm_source_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_source s on r.bgm_source_seq = s.bgm_source_seq";
                        strSqlGroupBy = " group by e.bgm_source_seq"
                            + ", s.bgm_source_name";
                        break;
                    case "5":
                        strSqlSelect = " exp.bgm_exp_grp_seq"
                            + ", grp.bgm_exp_grp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense_group grp on exp.bgm_exp_grp_seq = grp.bgm_exp_grp_seq";
                        strSqlGroupBy = " group by exp.bgm_exp_grp_seq"
                            + ", grp.bgm_exp_grp_name";
                        break;
                    case "6":
                        strSqlSelect = " exp2.bgm_exp_seq"
                            + ", exp2.bgm_exp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense exp1 on exp.upper_bgm_exp_seq = exp1.bgm_exp_seq"
                            + " left join bgm_mas_expense exp2 on exp1.upper_bgm_exp_seq = exp2.bgm_exp_seq";
                        strSqlGroupBy = " group by exp2.bgm_exp_seq"
                            + ", exp2.bgm_exp_name";
                        break;
                    case "7":
                        strSqlSelect = " exp1.bgm_exp_seq"
                            + ", exp1.bgm_exp_name as name"
                            + ", coalesce(sum(coalesce(e.total_amount, 0)), 0) as total_amount";
                        strSqlJoin = " left join bgm_mas_expense exp1 on exp.upper_bgm_exp_seq = exp1.bgm_exp_seq";
                        strSqlGroupBy = " group by exp1.bgm_exp_seq"
                            + ", exp1.bgm_exp_name";
                        break;
                }

                string sql = "select " + strSqlSelect
                            + " from bgm_receive r"
                            + " left join bgm_receive_expense e on r.bgm_rec_seq = e.bgm_rec_seq"
                            + " left join bgm_mas_expense exp on e.bgm_exp_seq = exp.bgm_exp_seq"
                            + strSqlJoin
                            + " where r.record_status not like @RecordStatus"
                            + (Year.Length > 0 ? " and r.bgm_year = @Year" : "")
                            + (Jobsite.Length > 0 ? " and r.job_site_seq = @Jobsite" : "")
                            + (SubAct.Length > 0 ? " and r.bgm_strat_list_seq = @SubAct" : "")
                            + (Project.Length > 0 ? " and r.bgm_rec_seq = @Project" : "")
                            + (Source.Length > 0 ? " and e.bgm_source_seq = @Source" : "")
                            + (ExpenseGroup.Length > 0 ? " and exp.bgm_exp_grp_seq = @ExpenseGroup" : "")
                            + strSqlGroupBy;

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListProjectExpenseGrid(string app_header_seq, string source, string exp_grp)
        {
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@seq", app_header_seq);
                dbManager.AddParameters(2, "@source", source);
                dbManager.AddParameters(3, "@expgrp", exp_grp);

                string sql = " select e.bgm_exp_seq as value, e.bgm_exp_name as text"
                            + ", eg.bgm_exp_grp_seq, concat(eg.bgm_exp_grp_code, '-' , eg.bgm_exp_grp_name) as bgm_exp_grp_name"
                        + " from bgm_mas_expense e"
                            + " inner join bgm_mas_expense_group eg on e.bgm_exp_grp_seq = eg.bgm_exp_grp_seq"
                        + " where e.bgm_exp_grp_seq = @expgrp"
                            + " and e.bgm_exp_seq not in ( select upper_bgm_exp_seq from bgm_mas_expense where record_status = @RecordStatus and upper_bgm_exp_seq is not null)"
                            + " and e.record_status = @RecordStatus"
                        + " group by e.bgm_exp_seq, e.bgm_exp_name, eg.bgm_exp_grp_seq, eg.bgm_exp_grp_code, eg.bgm_exp_grp_name"
                        + " order by eg.bgm_exp_grp_code, e.bgm_exp_name";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ApproveTransaction(string HeaderID)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@HeaderID", HeaderID);
                string strsql = "DELETE FROM bgm_approval_control_expense WHERE bgm_app_ctrl_exp_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
